package wawi.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import org.apache.log4j.Logger;

import wawi.util.WawiLogger;

public class DbAutomaticUpdate
{
    private static Logger l = Logger.getLogger(DbAutomaticUpdate.class);
    
    public static void execute(Connection conn)
    {
    	WawiLogger.init();
    	
        try
        {
        	journal_wareneingang(conn);
        	updateVerkaufPos(conn);
        	updateVerkaufHinweis(conn);
        	artikel_umbuchung(conn);
        	gutschrift(conn);
        	journal_artikelStorno(conn);
        	gutschriftVerrechnung(conn);
        }
        catch (Exception e)
        {
            l.error("Fehler beim DBUpdate: " +e);
        }
    }
    
	
    private static void journal_wareneingang(Connection conn) throws Exception
    {
        if (notExistsTable(conn, "JRN_WARENEINGANG"))
        {            
            String sql = "CREATE TABLE \"JRN_WARENEINGANG\" (\"RECID\" INTEGER NOT NULL PRIMARY KEY, " +
            		"\"DATUM\" TIMESTAMP NOT NULL, \"LAGER\" INTEGER NOT NULL, " +
                   	"\"BESTELLUNG\" INTEGER NOT NULL, " +
                    "\"EAN\" INTEGER NOT NULL, " +
                    "\"MENGE\" INTEGER NOT NULL)";                        
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "alter table JRN_WARENEINGANG add constraint FK_JRN_WARENEINGANG_1 foreign key (BESTELLUNG) references BESTELLUNG(RECID)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "alter table JRN_WARENEINGANG add constraint FK_JRN_WARENEINGANG_2 foreign key (EAN) references ARTIKEL_DETAILS(EAN)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE GENERATOR GEN_JRN_WARENEINGANG_ID";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE TRIGGER JRN_WARENEINGANG_BI FOR JRN_WARENEINGANG ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.RECID IS NULL) THEN NEW.RECID = GEN_ID(GEN_JRN_WARENEINGANG_ID,1); END";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
        }
    }
    
    
    
    
    private static void updateVerkaufPos(Connection conn) throws Exception
    {
        if(notExistsFieldInTable(conn, "PREISANGEPASST", "VERKAUF_POS"))
        {
            String sql = "ALTER TABLE \"VERKAUF_POS\" ADD \"PREISANGEPASST\" INTEGER DEFAULT 0 NOT NULL";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "UPDATE \"VERKAUF_POS\" SET PREISANGEPASST=0";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
        }
    }

    private static void updateVerkaufHinweis(Connection conn) throws Exception
    {
        if(notExistsFieldInTable(conn, "HINWEIS", "VERKAUF"))
        {
            String sql = "ALTER TABLE \"VERKAUF\" ADD \"HINWEIS\" VARCHAR(200) DEFAULT '' NOT NULL";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "UPDATE \"VERKAUF\" SET HINWEIS=''";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
        }
    }
    
	
    private static void artikel_umbuchung(Connection conn) throws Exception
    {
        if (notExistsTable(conn, "ARTIKEL_UMBUCHUNG"))
        {            
            String sql = "CREATE TABLE \"ARTIKEL_UMBUCHUNG\" (\"RECID\" INTEGER NOT NULL PRIMARY KEY, " +
            		"\"EAN\" INTEGER NOT NULL, " +
            		" \"STATUS\" INTEGER NOT NULL, " +
            		" \"VON\" INTEGER NOT NULL, " +
            		" \"NACH\" INTEGER NOT NULL, " +
            		"\"DATUM_START\" TIMESTAMP," +
            		"\"DATUM_ENDE\" TIMESTAMP)";                        
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);            
            
            sql = "alter table ARTIKEL_UMBUCHUNG add constraint FK_ARTIKEL_UMBUCHUNG_1 foreign key (EAN) references ARTIKEL_DETAILS(EAN)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE GENERATOR GEN_ARTIKEL_UMBUCHUNG_ID";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE TRIGGER ARTIKEL_UMBUCHUNG_BI FOR ARTIKEL_UMBUCHUNG ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.RECID IS NULL) THEN NEW.RECID = GEN_ID(GEN_ARTIKEL_UMBUCHUNG_ID,1); END";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
        }
    }
    
    
    
    private static void gutschrift(Connection conn) throws Exception
    {
        if (notExistsTable(conn, "GUTSCHRIFT"))
        {            
            String sql = "CREATE TABLE \"GUTSCHRIFT\" (\"RECID\" INTEGER NOT NULL PRIMARY KEY, " +
            		"\"DATUM\" TIMESTAMP NOT NULL," +
            		"\"STATUS\" INTEGER NOT NULL, " +
            		" \"BETRAG\" FLOAT NOT NULL, " +
            		" \"LADEN\" INTEGER NOT NULL, " +
            		" \"HINWEIS\" VARCHAR(200) DEFAULT '' NOT NULL)";                     
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);            
            
            
            sql = "CREATE GENERATOR GEN_GUTSCHRIFT_ID";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE TRIGGER GUTSCHRIFT_BI FOR GUTSCHRIFT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.RECID IS NULL) THEN NEW.RECID = GEN_ID(GEN_GUTSCHRIFT_ID,1); END";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
        }
    }
    
    
    private static void executeUpdate (Connection connection, String sql) throws Exception
    {
        if (connection != null)
        {
            Statement stmt = connection.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
        }
    }
    
    private static boolean notExistsTable(Connection connection, String tableName) throws Exception
    {
        DatabaseMetaData md = (DatabaseMetaData) connection.getMetaData();
        ResultSet rs = md.getTables("myschema",null,null,null);
        
        boolean ret = true;
        
        while(rs.next())
        {
            if (rs.getString("TABLE_NAME").equalsIgnoreCase(tableName))
            {
                ret = false;
                break;
            }
        }
        
        rs.close();        
        return ret;
    }
    
    private static boolean notExistsFieldInTable (Connection connection, String field, String tableName) throws Exception
    {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(String.format("SELECT FIRST 1 * FROM %s", tableName));
        ResultSetMetaData rsMd = rs.getMetaData();
        
        boolean ret = true;
        
        for (int i=0; i<rsMd.getColumnCount(); i++)
        {
            if (rsMd.getColumnName(i+1).equalsIgnoreCase(field))
            {
                ret = false;
                break;
            }
        }
        
        rs.close();
        stmt.close();
        
        return ret;
    }
    
    private static void journal_artikelStorno(Connection conn) throws Exception
    {
        if (notExistsTable(conn, "JRN_ARTIKEL_STORNO"))
        {            
            String sql = "CREATE TABLE \"JRN_ARTIKEL_STORNO\" (\"RECID\" INTEGER NOT NULL PRIMARY KEY, " +
            		"\"DATUM\" TIMESTAMP NOT NULL, " +
            		"\"LAGER\" INTEGER NOT NULL, " +
                   	"\"VERKAUF\" INTEGER NOT NULL, " +
                    "\"EAN\" INTEGER NOT NULL, " +
                    "\"MENGE\" INTEGER NOT NULL)";                        
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "alter table JRN_ARTIKEL_STORNO add constraint FK_JRN_ARTIKEL_STORNO_1 foreign key (VERKAUF) references VERKAUF(RECID)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "alter table JRN_ARTIKEL_STORNO add constraint FK_JRN_ARTIKEL_STORNO_2 foreign key (EAN) references ARTIKEL_DETAILS(EAN)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE GENERATOR GEN_JRN_ARTIKEL_STORNO_ID";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "CREATE TRIGGER JRN_ARTIKEL_STORNO_BI FOR JRN_ARTIKEL_STORNO ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.RECID IS NULL) THEN NEW.RECID = GEN_ID(GEN_JRN_ARTIKEL_STORNO_ID,1); END";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
        }
    }
    
    private static void gutschriftVerrechnung(Connection conn) throws Exception
    {
        if (notExistsTable(conn, "GUTSCHRIFT_VERRECHNUNG"))
        {            
            String sql = "CREATE TABLE \"GUTSCHRIFT_VERRECHNUNG\" ("+
            		"\"VERKAUF\" INTEGER NOT NULL, " +
            		"\"GUTSCHRIFT\" INTEGER NOT NULL, " +
            		"\"DATUM\" TIMESTAMP NOT NULL)";               
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);       
            
            
            sql = "alter table GUTSCHRIFT_VERRECHNUNG add constraint PK_GUTSCHRIFT_VERRECHNUNG primary key (VERKAUF,GUTSCHRIFT)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            
            sql = "alter table GUTSCHRIFT_VERRECHNUNG add constraint FK_GUTSCHRIFT_VERRECHNUNG_1 foreign key (VERKAUF) references VERKAUF(RECID)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            sql = "alter table GUTSCHRIFT_VERRECHNUNG add constraint FK_GUTSCHRIFT_VERRECHNUNG_2 foreign key (GUTSCHRIFT) references GUTSCHRIFT(RECID)";
            executeUpdate(conn, sql);
            l.info("OK -> " + sql);
            
            
        }
    }
    
    private static boolean existsFieldInTableAndHasType (Connection connection, String field, String tableName, String type) throws Exception
    {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(String.format("SELECT FIRST 1 * FROM %s", tableName));
        ResultSetMetaData rsMd = rs.getMetaData();
        
        boolean ret = false;
        
        for (int i=0; i<rsMd.getColumnCount(); i++)
        {
            if (rsMd.getColumnName(i+1).equalsIgnoreCase(field))
            {
                ret = !rsMd.getColumnTypeName(i+1).equalsIgnoreCase(type);
                break;
            }
        }
        
        rs.close();
        stmt.close();
        
        return ret;
    }
}
